﻿using Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Dapper;
using Dapper.Mapper;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{

    /// <summary>
    /// 数据访问类:DepartmentDAL
    /// </summary>
    public partial class DepartmentDAL
    {
        public IDbConnection Conn
        {
            get
            {
                return DBUtility.MySqlHelper.Conn;
            }
        }

        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int? Insert(DepartmentModel model)
        {
            try
            {
                using (Conn)
                {
                    string insertQuery = @"INSERT INTO ms_department (Id,DepartmentNo,DepartmentName,Remarks) VALUES (@Id,@DepartmentNo,@DepartmentName,@Remarks)";
                    var result = Conn.Execute(insertQuery, model);
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(DepartmentModel model)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Update(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(string id)
        {
            try
            {
                using (Conn)
                {
                    DepartmentModel model = new DepartmentModel();
                    model.Id = id;
                    return Conn.Delete(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(DepartmentModel model)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Delete(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据ID得到一个对象实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DepartmentModel Get(string id)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Get<DepartmentModel>(id); 
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询多条数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<DepartmentModel> GetList()
        {
            try
            {
                using (Conn)
                {
                    return Conn.GetList<DepartmentModel>();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据EmployeeNo查询数据
        /// </summary>
        /// <returns></returns>
        public DepartmentModel GetModelByDepartmentNo(string departmentNo)
        {
            try
            {
                using (Conn)
                {
                    string insertQuery = @"select * from ms_department where DepartmentNo=@DepartmentNo";
                    var para = new DynamicParameters();
                    para.Add("DepartmentNo", departmentNo);
                    var result = Conn.QuerySingleOrDefault<DepartmentModel>(insertQuery, para);
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据Id和编码查询数据
        /// </summary>
        /// <returns></returns>
        public int IsExistByIdAndEmployeeNo(string Id, string DepartmentNo)
        {
            try
            {
                using (Conn)
                {
                    string query = "select * from ms_department where DepartmentNo=@DepartmentNo and Id<>@Id";
                    var para = new DynamicParameters();
                    para.Add("Id", Id);
                    para.Add("DepartmentNo", DepartmentNo);                    
                    DepartmentModel model= Conn.QuerySingleOrDefault<DepartmentModel>(query,para);
                    if (model != null)
                    {
                        return 1;
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据Id和编码查询数据
        /// </summary>
        /// <returns></returns>
        public int IsExistByDepartmentNo(string DepartmentNo)
        {
            try
            {
                using (Conn)
                {
                    string query = "select * from ms_department where DepartmentNo=@DepartmentNo";
                    var para = new DynamicParameters();
                    para.Add("DepartmentNo", DepartmentNo);
                    DepartmentModel model = Conn.QuerySingleOrDefault<DepartmentModel>(query,para);
                    if (model != null)
                    {
                        return 1;
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="employeeNo"></param>
        /// <param name="employeeName"></param>
        /// <returns></returns>
        public IEnumerable<DepartmentModel> GetModelListByNoAndName(string departmentNo, string departmentName)
        {
            try
            {
                using (Conn)
                {                  
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("select * from ms_department where 1=1");
                    var para = new DynamicParameters();
                    if (!string.IsNullOrEmpty(departmentNo))
                    {
                        strSql.Append(" and DepartmentNo like @DepartmentNo");
                        para.Add("DepartmentNo", "%" + departmentNo + "%");
                    }
                    if (!string.IsNullOrEmpty(departmentName))
                    {
                        strSql.Append(" and DepartmentName like @DepartmentName");
                        para.Add("DepartmentName", "%" + departmentName + "%");
                    }
                    strSql.Append(" Order by DepartmentNo asc ");
                    return Conn.Query<DepartmentModel>(strSql.ToString(), para);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DepartmentModel DataRowToModel(DataRow row)
        {
            DepartmentModel model = new DepartmentModel();
            if (row != null)
            {
                if (row["Id"] != null)
                {
                    model.Id = row["Id"].ToString();
                }
                if (row["DepartmentNo"] != null)
                {
                    model.DepartmentNo = row["DepartmentNo"].ToString();
                }
                if (row["DepartmentName"] != null)
                {
                    model.DepartmentName = row["DepartmentName"].ToString();
                }
                if (row["Remarks"] != null)
                {
                    model.Remarks = row["Remarks"].ToString();
                }
            }
            return model;
        }

        

        
    }
}


